package org.liulinlin.sejw.box.alloc;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;

import org.liulinlin.sejw.db.*;
import org.liulinlin.sejw.box.*;

import org.osoa.sca.annotations.Destroy;
import org.osoa.sca.annotations.Init;

public class MakeBoxAllocFormServiceImpl implements MakeBoxAllocFormService {
	private DBConnectionManager connMgr;
	private BAF2SN baf2SN;
	
	@Init
	public void init() {
		connMgr = DBConnectionManager.getInstance();
		baf2SN = BAF2SN.getInstance();
	}
	
	@Destroy
	public void destroy() {
		connMgr.release();
	}
	
	/**
	 * 得到id为id的舱位分配清单
	 */
	public BoxAllocForm getBoxAllocForm(String id) {
		BoxAllocForm res = null;
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return null;
		}
		
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select count(*) from BoxAllocForm where BAF_ID = '" + id + "'");
		
			rs.next();
			if (rs.getInt(1) <= 0) {
				newBoxAllocForm(id);
			}
			res = new BoxAllocForm(id);
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
	/**
	 * 新建一个id为id的舱位分配清单
	 * @param id
	 */
	public void newBoxAllocForm(String id) {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return;
		}
		
		try {
			Statement stmtBAF = con.createStatement();
			ResultSet rsBAF = stmtBAF.executeQuery("select count(*) from BoxAllocForm where BAF_ID = '" + id + "'");
			rsBAF.next();
			if (rsBAF.getInt(1) > 0) {
				rsBAF.close();
				stmtBAF.close();
				connMgr.freeConnection("shipping_export", con);
				return;
			}
			rsBAF.close();
			stmtBAF.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("insert into BoxAllocDetails values(null, 0, 0, 0, 0)");
			ResultSet rs = stmt.executeQuery("select LAST_INSERT_ID() from BoxAllocDetails");
		
			rs.next();
			stmt.executeUpdate("insert into BoxAllocForm values(" + id + ", " + rs.getInt(1) + ")");
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		
		connMgr.freeConnection("shipping_export", con);
	}
	
	/**
	 * 添加新分配详情
	 * @param commID
	 * @param bigNum
	 * @param smallNum
	 * @param BAFID
	 */
	public int putAllocDetails(String commID, int bigNum, int smallNum, String BAFID) {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return -1;
		}
		
		int res = -1;
		
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("insert into AllocDetails values(null, '" + commID + "', " + bigNum + ", " + smallNum + ", 1, '" + BAFID + "')");
		
			ResultSet rsAD = stmt.executeQuery("select LAST_INSERT_ID() from AllocDetails");
		
			rsAD.next();
			res = rsAD.getInt(1);
			
			rsAD.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
	/**
	 * 删除分配详情
	 */
	public void deleteAllocDetails(int id) {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return;
		}
		
		String BAFID;
		int bigNum, smallNum, status;
		
		try {
			Statement stmt = con.createStatement();
			//更新集装箱分配详情
			ResultSet rsAD = stmt.executeQuery("select * from AllocDetails where AD_ID = '" + id + "'");
		
			rsAD.next();
			bigNum = rsAD.getInt("AD_BigNum");
			smallNum = rsAD.getInt("AD_SmallNum");
			status = rsAD.getInt("AD_Status");
			BAFID = rsAD.getString("BAF_ID");
			
			if (status > 2) {		//若已制十联单或订舱成功，则更新集装箱分配详情
				ResultSet rsBAF = stmt.executeQuery("select * from BoxAllocForm where BAF_ID = '" + BAFID + "'");
				rsBAF.next();
				int BADID = rsBAF.getInt("BAD_ID");
				ResultSet rsBAD = stmt.executeQuery("select * from BoxAllocDetails");
				int orderedBigNum = rsBAD.getInt("BAD_OrderedBigNum") - bigNum;
				int orderedSmallNum = rsBAD.getInt("BAD_OrderedSmallNum") - smallNum;
				
				stmt.executeUpdate("update BoxAllocDetails set BAD_OrderedBigNum = '" + orderedBigNum + "', set BAD_OrderedSmallNum = '" + orderedSmallNum + "' where BAD_ID = '" + BADID + "'");
				
				if (status == 4) {
					int passedBigNum = rsBAD.getInt("BAD_PassedBigNum") - bigNum;
					int passedSmallNum = rsBAD.getInt("BAD_PassedSmallNum") - smallNum;
					stmt.executeUpdate("update BoxAllocDetails set BAD_PassedBigNum = '" + passedBigNum + "', set BAD_PassedSmallNum = '" + passedSmallNum + "' where BAD_ID = '" + BADID + "'");
				}
				
				rsBAF.close();
				rsBAD.close();
			}
			
			rsAD.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("delete from AllocDetails where AD_ID = '" + id + "'");
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		connMgr.freeConnection("shipping_export", con);
	}
	/**
	 * 更新分配详情
	 * @param id
	 * @param big
	 * @param small
	 * @param status
	 */
	public void updateAllocDetails(int id, int big, int small, int status) {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return;
		}
		
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("update AllocDetails set AD_BigNum='" + big + "', AD_SmallNum='" + small + "', AD_Status='" + status + "' where AD_ID='" + id + "'");
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connMgr.freeConnection("shipping_export", con);
	}
	/**
	 * 得到未处理完的订舱委托单且能够符合预配计划
	 * @return
	 */
	public List<Commission> getUnDoneComm(String BAFID) {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return null;
		}
		
		List<Commission> res = new ArrayList<Commission>();
		
		try {
			Statement stmt = con.createStatement();
			ResultSet rsComm = stmt.executeQuery("select Comm_ID from Commission");
		
			while (rsComm.next()) {
				Commission comm = new Commission(rsComm.getString("Comm_ID"));
				if (unDone(comm) && fitOrderPlan(comm, BAFID)) {
					res.add(comm);
				}
			}
			
			rsComm.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		
		connMgr.freeConnection("shipping_export", con);
		return res;
	}
	/**
	 * 判断订舱委托单是否被处理完
	 * @param comm
	 * @return
	 */
	private boolean unDone(Commission comm) {
		//20GP是20尺小箱，40GP是40尺大箱，40HQ是40尺高箱
		//这里只根据大小箱计算，暂时忽略高箱		
		return getUnAllocBoxNum(comm, "20GP") != 0 || getUnAllocBoxNum(comm, "40GP") != 0;
	}
	/**
	 * 判断订舱委托单能否由某个预配计划分配舱位
	 * @param comm
	 * @param orderPlanID
	 * @return
	 */
	private boolean fitOrderPlan(Commission comm, String BAFID) {
		BoxAllocForm boxAllocForm = new BoxAllocForm(BAFID);
		OrderPlan orderPlan = new OrderPlan(BAFID);
		
		//船公司
		if (!orderPlan.getShipSchedule().getShipInfo().getShipComInfo().getId().equals(comm.getShipComInfo().getId())) {
			return false;
		}
		//航线
		if (comm.getRoute() != null && !comm.getRoute().equals(orderPlan.getShipSchedule().getRoute())) {
			return false;
		}
		//启运港
		if (!comm.getDeparture().equals(orderPlan.getShipSchedule().getStarter())) {
			return false;
		}
		//目的港
		if (!comm.getDestination().equals(orderPlan.getShipSchedule().getDestination())) {
			return false;
		}
		//开船时间
		if (comm.getDepartureTime() != null 
				&& comm.getDepartureTime().compareTo(orderPlan.getShipSchedule().getLeaveTime()) != 0
				&& !comm.getTimeChangable()) {
			return false;
		}
		//抵港时间
		if (comm.getArriveTime() != null 
				&& comm.getArriveTime().compareTo(orderPlan.getShipSchedule().getArriveTime()) != 0
				&& !comm.getTimeChangable()) {
			return false;
		}
		//货物
		if (getUnAllocBoxNum(comm, "40GP") > orderPlan.getBigBox() - boxAllocForm.getBoxAllocDetails().getOrderedBigNum()
				&& !comm.getMultiSchedule() && !comm.getMultiShip()) {
			return false;
		}
		if (getUnAllocBoxNum(comm, "20GP") > orderPlan.getTotalBox() - orderPlan.getBigBox() - boxAllocForm.getBoxAllocDetails().getOrderedSmallNum()
				&& !comm.getMultiSchedule() && !comm.getMultiShip()) {
			return false;
		}
		return true;
	}
	/**
	 * 计算comm中未分配舱位的箱型数量
	 * @param comm
	 * @param type
	 * @return
	 */
	public int getUnAllocBoxNum(Commission comm, String type) {
		int remain = -1;
		ResultSet rs;
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return -1;
		}
		
		if (type.equals("20GP")) {
			try {
				Statement stmt = con.createStatement();
				remain = comm.getGoods().getGoodsNum().getGP20();
				rs = stmt.executeQuery("select * from AllocDetails where Comm_ID = '" + comm.getId() + "'");
			
				while (rs.next()) {
					remain -= rs.getInt("AD_SmallNum");
				}
				
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				//shutdown();
			}
		} else if (type.equals("40GP")) {
			try {
				Statement stmt = con.createStatement();
				remain = comm.getGoods().getGoodsNum().getGP40();
				rs = stmt.executeQuery("select * from AllocDetails where Comm_ID = '" + comm.getId() + "'");
			
				while (rs.next()) {
					remain -= rs.getInt("AD_BigNum");
				}
				
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				//shutdown();
			}
		} 
		
		connMgr.freeConnection("shipping_export", con);
		
		return remain;
	}
	
	/**
	 * 设置传递给缮制十联单页面的参数
	 */
	public void setBookBoxParams(String orderPlanID, int ADID) {
		baf2SN.setOrderPlanId(orderPlanID);
		baf2SN.setADID(ADID);
	}
}
